package com.taskcollection.service;

import com.taskcollection.bean.ProjectClassifyBean;
import com.taskcollection.util.DBConnection;
import com.taskcollection.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2016/7/23 0023.
 */
public class ProjectClassifyService {
    public List<ProjectClassifyBean> getAll(String Teacher_id){
        //TODO:获取所有项目分类
        List<ProjectClassifyBean> listBean = new ArrayList<ProjectClassifyBean>();
        Connection conn = DBConnection.getinstance().getConnection();
        String sql = "SELECT * from projectclassify WHERE teacher_id=?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            ps = conn.prepareStatement(sql);
            ps.setString(1,Teacher_id);
            rs=ps.executeQuery();
            while (rs.next()){
                ProjectClassifyBean pcb = new ProjectClassifyBean();
                pcb.setProjectClassify_name(rs.getString("projectClassify_name"));
                pcb.setProjectClassify_id(rs.getString("ProjectClassify_id"));
                listBean.add(pcb);
            }
            return listBean;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResultSet(rs);
            DBUtil.closePreparedStatement(ps);
            DBUtil.closeConnection(conn);
        }
        return listBean;

    }
    public boolean add(String name,String Teacher_id){
        //TODO:添加项目分类
        String sql = "insert into projectclassify(projectClassify_id,projectClassify_name,teacher_id) values(?,?,?)";
        Connection conn = DBConnection.getinstance().getConnection();
        PreparedStatement ps =null;
        try{
            ps = conn.prepareStatement(sql);
            ps.setString(1,ServiceUtil.getPK("projectClassify_id","projectclassify"));
            ps.setString(2,name);
            ps.setString(3,Teacher_id);
            ps.execute();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closePreparedStatement(ps);
            DBUtil.closeConnection(conn);
        }
        return false;
    }
    public boolean change(String id,String name){
        //TODO:修改项目分类
        String sql = "UPDATE projectclassify set projectClassify_name=? WHERE ProjectClassify_id=?";
        Connection conn = DBConnection.getinstance().getConnection();
        PreparedStatement ps = null;
        try{
            ps = conn.prepareStatement(sql);
            ps.setString(1,name);
            ps.setString(2,id);
            ps.execute();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    public boolean delete(String id){
        //TODO:删除项目分类
        String sql = "DELETE from projectclassify WHERE ProjectClassify_id=?";
        Connection conn = DBConnection.getinstance().getConnection();
        PreparedStatement ps = null;
        try{
            ps = conn.prepareStatement(sql);
            ps.setString(1,id);
            ps.execute();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    public ProjectClassifyBean getById(String id){
        String sql = "select * from projectclassify where ProjectClassify_id=?";
        Connection conn = DBConnection.getinstance().getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        ProjectClassifyBean pcb = new ProjectClassifyBean();
        try{
            ps=conn.prepareStatement(sql);
            ps.setString(1,id);
            rs = ps.executeQuery();
            if(rs.next()){
                pcb.setProjectClassify_name(rs.getString("projectClassify_name"));
                pcb.setProjectClassify_id(rs.getString("ProjectClassify_id"));
            }
            return pcb;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeResultSet(rs);
            DBUtil.closePreparedStatement(ps);
            DBUtil.closeConnection(conn);
        }
        return null;
    }
}
